/**
 * 
 */
package org.compiere.process;

import java.io.File;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Properties;
import java.util.TimeZone;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MBPartnerLocation;
import org.compiere.model.MOrg;
import org.compiere.model.Mhrmpayment;
import org.compiere.model.X_C_BPartner;
import org.compiere.util.Env;
import org.compiere.util.Language;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Employee;
import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

/**
 * @author A.Yerzhanov
 * 24.12.2012
 */
public class GenerateWagesInfo extends SvrProcess {	

	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private String path = "";
	/** Get month and year */
	Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
	SimpleDateFormat dateFormat = new SimpleDateFormat("ddMMyyyy");
	
	private int bpartnerID = 0;
	private Timestamp startDate = null;
	private Timestamp endDate = null;
	
	
	@Override
	protected void prepare() {		
	
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
				
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name.equals("C_BPartner_ID"))
				bpartnerID = Integer.parseInt(para[i].getParameter().toString());
			else if (name.compareTo("DateFrom") == 0){				
				startDate = (Timestamp) para[i].getParameter();				
				endDate = (Timestamp) para[i].getParameter_To();
			}
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter() + System.getProperty("file.separator");
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
	}

	@Override
	protected String doIt() throws Exception {
		
		// Types of cells for
		Label label;
		Number number;	
		
		// Template variables
		double total = 0;
		double totalOPV = 0;
		double totalIPN = 0;
		
		double ipn = 0;
		double opv = 0;
		double sum = 0;
		
		// Define employee identification
		MBPartner bp = new MBPartner(getCtx(), bpartnerID, get_TrxName());		
		
		// There are checks for a specified pattern
		String templateName = " Справка_о_ЗП_" + bp.getLastName() + "_за_период_с_" + dateFormat.format(startDate) + "_по_" + dateFormat.format(endDate);
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate") + " " + templateName);
				
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append(templateName).append(fileExtension);
		String sampleTemp = Util.localFilePath + "AdempiereTemp";
		
		//
		File templateCopy = new File(fullPath.toString());
		File sampleCopy = new File(sampleTemp);
		File temporaryFile = entry.getFile(Util.localFilePath + templateName);
		File temporarySample = entry.getFile(Util.localFilePath + templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		// for sample
		Workbook sample = null;
		WritableWorkbook samCopy = null;
		WritableSheet sheet_1 = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
			 // for sample
			 sample = Workbook.getWorkbook(temporarySample);
			 samCopy = Workbook.createWorkbook(sampleCopy, sample);
			 sheet_1 = samCopy.getSheet(0);
			 samCopy.write();
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
			return "Error tableBook.";
		}
		 					
       // Title style cell
       WritableFont fontTitle8 = new WritableFont (WritableFont.TAHOMA, 8, WritableFont.BOLD);
       WritableCellFormat titleStyle = new WritableCellFormat(fontTitle8);
       titleStyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
       titleStyle.setAlignment(Alignment.CENTRE);
       titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       
       // Main style cell
       WritableFont fontTitle7 = new WritableFont (WritableFont.TAHOMA, 8, WritableFont.NO_BOLD);
       WritableCellFormat mainStyle = new WritableCellFormat(fontTitle7);
	   mainStyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
	   mainStyle.setAlignment(Alignment.CENTRE);
	   mainStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	   mainStyle.setWrap(true);
	   
	   // Header style
       WritableCellFormat headStyle = new WritableCellFormat(fontTitle8);
       headStyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
       headStyle.setAlignment(Alignment.LEFT);
       headStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
	   
	   // Standard style cell
	   WritableCellFormat borderStyle = new WritableCellFormat();
	   borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	   borderStyle.setAlignment(Alignment.CENTRE);
	   borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	   borderStyle.setWrap(true);
	   
	   // Number style
	   WritableCellFormat numberStyle = new WritableCellFormat(NumberFormats.THOUSANDS_FLOAT);
	   numberStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	   numberStyle.setAlignment(Alignment.CENTRE);
	   numberStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	   numberStyle.setWrap(true); 
	   
	   // Date style	   
       WritableCellFormat dateStyle = new WritableCellFormat(fontTitle7);
	   dateStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	   dateStyle.setAlignment(Alignment.LEFT);
	   dateStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	   dateStyle.setWrap(true);
	   
	   /** Body begin */ 
	   // Define organization identification 
	   MOrg morg = new MOrg(getCtx(), Env.getAD_Org_ID(getCtx()), get_TrxName());
	   int orgID = morg.getLinkedC_BPartner_ID(get_TrxName());
	   MBPartner orgbp = new MBPartner(getCtx(), orgID, get_TrxName());
	   String orgname = MBPartnerLocation.getBPName(orgID);
	   
	   
	   // Title
	   X_HR_Employee he = X_HR_Employee.getHR_Employee(bp.getC_BPartner_ID(),true);
	   if (he.getC_BPartner_ID() == 0)
		   throw new AdempiereException ("Info about employee was empty");
	   
	   ExcelCell cellStart =  Util.getCellStart(tableBook,">>");	 
	   
	   /** Clear all cell */	   
	   //label = new Label(cellTitle.getC(), cellTitle.getR(), " ");
	   //sheet.addCell(label);
	   
	   /** Fill title */	   
	   label = new Label(1, 1, Env.getContext(getCtx(), "#AD_Org_Name"), titleStyle);
	   sheet.addCell(label);	   
	   label = new Label(1, 3, orgname, mainStyle);
	   sheet.addCell(label);	   	   
	   label = new Label(3, 5, orgbp.getFI_TIN(), titleStyle); 
	   sheet.addCell(label);	   	   
	   label = new Label(3, 6, new org.joda.time.DateTime(endDate.getTime()).getDayOfMonth() + " " + Util.getMonthName(endDate, Language.AD_Language_ru_RU, true) + " " + new org.joda.time.DateTime(endDate.getTime()).getYear() + " г.", titleStyle);
	   sheet.addCell(label);
	   //
	   sheet.mergeCells(2, 12, 14, 12);
	   label = new Label(2, 12, bp.getName() + ", таб. № " + bp.gethrm_tableNumber() + ", РНН " + bp.getFI_TIN(), headStyle);
	   sheet.addCell(label);
	   sheet.mergeCells(2, 13, 14, 13);
	   label = new Label(2, 13, Env.getContext(getCtx(), "#AD_Org_Name"), headStyle);	   
	   sheet.addCell(label);
	   sheet.mergeCells(2, 14, 14, 14);
	   label = new Label(2, 14, he.getHR_Department().getName() + ", " + he.getHR_Job().getName(), headStyle);
	   sheet.addCell(label);	   
	   label = new Label(2, 15, Util.getMonthName(startDate, Language.AD_Language_ru_RU, false) + " " + new org.joda.time.DateTime(startDate.getTime()).getYear(), titleStyle);	   
	   sheet.addCell(label);
	   label = new Label(5, 15, Util.getMonthName(endDate, Language.AD_Language_ru_RU, false) + " " + new org.joda.time.DateTime(endDate.getTime()).getYear(), titleStyle);
	   sheet.addCell(label); 
 
	   /** Calculate wages */	 
	   if(new org.joda.time.DateTime(startDate.getTime()).getDayOfMonth() <= 1)
			calendar.setTime(new org.joda.time.DateTime(startDate.getTime()).plusDays(5).toDate());
		else
			calendar.setTimeInMillis(startDate.getTime());
		Timestamp dt = new Timestamp(calendar.getTimeInMillis());
		
	   int cntMonth = Util.getMonth(startDate, endDate) + 1;
	   for (int i = 0; i < cntMonth; i++){
		  if (i != (cntMonth - 1))
			  sheet.insertRow(cellStart.getR() + i);
		  
		  label = new Label(cellStart.getC(), cellStart.getR() + i, Util.getMonthName(dt, Language.AD_Language_ru_RU, false) + " " + new org.joda.time.DateTime(dt.getTime()).getYear(), dateStyle);
		  sheet.addCell(label);
		  
		  sum = Mhrmpayment.getSalarySum_Result(bpartnerID, dt).doubleValue();
		  ipn = Mhrmpayment.getSalarySum_IPN(bpartnerID, dt).doubleValue();
		  opv = Mhrmpayment.getSalarySum_OPV(bpartnerID, dt).doubleValue();
		  
		  if (dt.before(Timestamp.valueOf("2012-10-01 00:00:00"))){
			  sum = opv * 10;
		  }
		  number = new Number(cellStart.getC() + 1, cellStart.getR() + i, sum, numberStyle);
		  sheet.addCell(number);
		  
		  sheet.mergeCells(cellStart.getC() + 2, cellStart.getR() + i, cellStart.getC() + 4, cellStart.getR() + i);
		  number = new Number(cellStart.getC() + 2, cellStart.getR() + i, opv, numberStyle);
		  sheet.addCell(number);
		  
		  sheet.mergeCells(cellStart.getC() + 5, cellStart.getR() + i, cellStart.getC() + 8, cellStart.getR() + i);
		  number = new Number(cellStart.getC() + 5, cellStart.getR() + i, ipn, numberStyle);
		  sheet.addCell(number);
		  
		  sheet.mergeCells(cellStart.getC() + 9, cellStart.getR() + i, cellStart.getC() + 14, cellStart.getR() + i);
		  number = new Number(cellStart.getC() + 9, cellStart.getR() + i, (sum - (opv + ipn)), numberStyle);
		  sheet.addCell(number);		  
		  
		  //
		  calendar.setTime(dt);		  
		  calendar.add(Calendar.MONTH, 1);
		  dt = new Timestamp(calendar.getTimeInMillis());
		  		  
		  total = total + (sum - (opv + ipn));
		  totalOPV = totalOPV + opv;
		  totalIPN = totalIPN + ipn;		  
		  
	   }
	   
	   //sheet.mergeCells(cellStart.getC() + 9, cellStart.getR() + cntMonth, cellStart.getC() + 14, cellStart.getR() + cntMonth);
	   number = new Number(cellStart.getC() + 1, cellStart.getR() + cntMonth, total + totalOPV + totalIPN, numberStyle);
	   sheet.addCell(number);
	   sheet.mergeCells(cellStart.getC() + 2, cellStart.getR() + cntMonth, cellStart.getC() + 4, cellStart.getR() + cntMonth);
	   number = new Number(cellStart.getC() + 2, cellStart.getR() + cntMonth, totalOPV, numberStyle);
	   sheet.addCell(number);
	   sheet.mergeCells(cellStart.getC() + 5, cellStart.getR() + cntMonth, cellStart.getC() + 8, cellStart.getR() + cntMonth);
	   number = new Number(cellStart.getC() + 5, cellStart.getR() + cntMonth, totalIPN, numberStyle);
	   sheet.addCell(number);
	   sheet.mergeCells(cellStart.getC() + 9, cellStart.getR() + cntMonth, cellStart.getC() + 14, cellStart.getR() + cntMonth);
	   number = new Number(cellStart.getC() + 9, cellStart.getR() + cntMonth, total, numberStyle);
	   sheet.addCell(number);
	   	   
	   /** Fill footer */	  	   
	   sheet.mergeCells(cellStart.getC(), cellStart.getR() + cntMonth + 5, cellStart.getC() + 3, cellStart.getR() + cntMonth + 5);
	   label = new Label(cellStart.getC(), cellStart.getR() + cntMonth + 5, " Заместитель Председателя Правления", headStyle);
	   sheet.addCell(label);
	   
	   sheet.mergeCells(cellStart.getC(), cellStart.getR() + cntMonth + 8, cellStart.getC() + 3, cellStart.getR() + cntMonth + 8);
	   label = new Label(cellStart.getC(), cellStart.getR() + cntMonth + 8, "  Главный бухгалтер", headStyle);
	   sheet.addCell(label);
	   
	   sheet.mergeCells(cellStart.getC(), cellStart.getR() + cntMonth + 11, cellStart.getC() + 3, cellStart.getR() + cntMonth + 11);
	   label = new Label(cellStart.getC(), cellStart.getR() + cntMonth + 11, "  Главный менеджер", headStyle);
	   sheet.addCell(label);
	   
	    /** Body end */
	   copy.write();
	   copy.close();
	   tableBook.close();
	   
	   samCopy.close();
	   sample.close();
	   sampleCopy.delete();
	   temporaryFile.delete();
	   temporarySample.delete();
	   
		// Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
	
		return Msg.translate(m_ctx, "Success");
	}

}
